﻿------创建人：王涛       创建时间：2010年8月26日 11：58-------
USE ASERDB
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='Proc_GetEaer_Items' AND TYPE='P')
BEGIN
  DROP PROCEDURE Proc_GetEaer_Items
END
GO
CREATE Procedure [dbo].[Proc_GetEaer_Items]
 	@PageIndex int,
	@PageSize int,
	@CName varchar(50),
	@EName varchar(100),
	@AnotherName varchar(100),
	@EAN varchar(20),
	@TypeCode varchar(10),
	@MonitorUnits varchar(50)
	
 AS
DECLARE @SQL nvarchar(3000)
DECLARE @RecordCount int,@P1 INT
DECLARE @Where VARCHAR(1000)
IF @PageIndex >1 SET @PageIndex = (@PageIndex-1)*@PageSize +1
SET @Where = ''
IF ((@CName IS NOT NULL) AND (RTRIM(LTRIM(@CName))<>''))
BEGIN
  SET @Where = @Where +  ' AND CName LIKE ''%'+ @CName + '%'''
END
IF ((@EName IS NOT NULL) AND (RTRIM(LTRIM(@EName))<>''))
BEGIN
  SET @Where = @Where +  ' AND EName LIKE ''%'+ @EName + '%'''
END
IF ((@AnotherName IS NOT NULL) AND (RTRIM(LTRIM(@AnotherName))<>''))
BEGIN
  SET @Where = @Where +  ' AND AnotherName LIKE ''%'+ @AnotherName + '%'''
END
IF ((@EAN IS NOT NULL) AND (RTRIM(LTRIM(@EAN))<>''))
BEGIN
  SET @Where = @Where +  ' AND EAN = '''+ @EAN +''' '
END
IF ((@TypeCode IS NOT NULL) AND (RTRIM(LTRIM(@TypeCode))<>''))
BEGIN
  SET @Where = @Where +  ' AND TypeCode = '''+ @TypeCode +''' '
END
IF ((@MonitorUnits IS NOT NULL) AND (RTRIM(LTRIM(@MonitorUnits))<>''))
BEGIN
  SET @Where = @Where +  ' AND MonitorUnits LIKE ''%'+ @MonitorUnits + '%'''
END
IF ((LEN(@Where)>0) AND (LEFT(@Where,5)=' AND')) 
BEGIN
	SET @Where = Right(@Where,LEN(@Where)-4)
END
SET @SQL = N'select *,C.Name as TypeName,O.Name as DangerSignName 
            from  Eaer_Item E
            left join HazardousChemicals H on H.ChemicalID=E.ItemID
            left join CommonCode C ON H.TypeCode =C.Code 
            left join CommonCode O ON H.DangerSign=O.Code ORDER BY ChemicalID DESC'
IF (LEN(@Where)>0)
BEGIN
  SET @SQL = 'select *,C.Name as TypeName,O.Name as DangerSignName 
            from  Eaer_Item E
            left join HazardousChemicals H on H.ChemicalID=E.ItemID
            left join CommonCode C ON H.TypeCode =C.Code 
            left join CommonCode O ON H.DangerSign=O.Code where ' + @Where + ' ORDER BY ChemicalID DESC'
END
EXEC sp_cursoropen @P1 OUTPUT, @SQL, @scrollopt = 1, @ccopt = 335873, @rowcount = @RecordCount OUTPUT 
IF (@P1 != 0)
BEGIN
  EXEC sp_cursorfetch @P1, 32, @PageIndex, @PageSize 
  EXEC sp_cursorclose @P1
END
select @RecordCount
print @sql
return @@ERROR
